-- This demo illustrates characters beyond the Unicode base plane which are 
-- encoded with surrogate pairs in UTF-16.
-- © Erland Sommarskog 2024.

-- First try a database which is not surrogate-ware.
USE Latin1_General_100_CS_AS
go
-- First five characters are the first five letters in the Gothic alphabet. (In
-- case you don't know: Gothic is extinct.) The last character is an emoticon.
-- Note how SQL Server reports the wrong number of characters, and the substring
-- leads to an encoding error, as SSMS only sees one half of a surrogate pair.
DECLARE @gothic nvarchar(20) = N'𐌰𐌱𐌲𐌳𐌴😕'
SELECT @gothic AS word, len(@gothic) AS charlen, 
       datalength(@gothic) AS bytelen, substring(@gothic, 1, 3) AS FirstThree
go

-- Now create a database with a surrogate-compatible collation and move there.
CREATE DATABASE Czech_100_CS_AS_SC COLLATE Czech_100_CS_AS_SC
go
USE Czech_100_CS_AS_SC
go
-- Now SQL Server returns length and substring correctly.
DECLARE @gothic nvarchar(20) = N'𐌰𐌱𐌲𐌳𐌴😕'
SELECT @gothic AS word, len(@gothic) AS charlen, 
       datalength(@gothic) AS bytelen, substring(@gothic, 1, 3) AS FirstThree
-- As an extra exercise, look at the binary representation. See if you can spot
-- the surrogate pairs! Remember that byte pairs are swapped!
-- SELECT convert(varbinary(40), @gothic)
go
-- Try to insert six characters into nvarchar(10). Should work, or?
DECLARE @t TABLE (word nvarchar(10) NOT NULL)
INSERT @t (word) VALUES (N'𐌰𐌱𐌲𐌳𐌴😕')

